{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "crazy-therapist",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('mssql+pymssql://sa:vnpy@localhost:1433/master', encoding='utf8', echo=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "incorporated-gnome",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import Column, Integer, String, DateTime, Float, Sequence,Index, create_engine, TIMESTAMP, DateTime\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "roman-system",
   "metadata": {},
   "outputs": [],
   "source": [
    "Base = declarative_base()\n",
    "class DbBarOverview(Base):\n",
    "    __tablename__ = 'DbBarOverview'\n",
    "\n",
    "    symbol = Column(String(255), primary_key=True)\n",
    "    exchange = Column(String(255), primary_key=True)\n",
    "    interval = Column(String(255), primary_key=True)\n",
    "    count = Column(Integer)\n",
    "    start = Column(DateTime)\n",
    "    end = Column(DateTime)\n",
    "class DbBarData(Base):\n",
    "    __tablename__ = 'DbBarData'\n",
    "\n",
    "    symbol = Column(String(255), primary_key=True)\n",
    "    exchange = Column(String(255), primary_key=True)\n",
    "    datetime = Column(DateTime, primary_key=True)\n",
    "    interval = Column(String(255), primary_key=True)\n",
    "\n",
    "    volume = Column(Float)\n",
    "    open_interest = Column(Float)\n",
    "    open_price = Column(Float)\n",
    "    high_price = Column(Float)\n",
    "    low_price = Column(Float)\n",
    "    close_price = Column(Float)\n",
    "    \n",
    "class DbTickData(Base):\n",
    "    __tablename__ = 'DbTickData'\n",
    "\n",
    "    symbol = Column(String(255), primary_key=True)\n",
    "    exchange = Column(String(255), primary_key=True)\n",
    "    datetime = Column(DateTime, primary_key=True)\n",
    "\n",
    "    name = Column(String(255))\n",
    "    volume = Column(Float)\n",
    "    open_interest = Column(Float)\n",
    "    last_price = Column(Float)\n",
    "    last_volume = Column(Float)\n",
    "    limit_up = Column(Float)\n",
    "    limit_down = Column(Float)\n",
    "\n",
    "    open_price = Column(Float)\n",
    "    high_price = Column(Float)\n",
    "    low_price = Column(Float)\n",
    "    pre_close = Column(Float)\n",
    "\n",
    "    bid_price_1 = Column(Float)\n",
    "    bid_price_2 = Column(Float)\n",
    "    bid_price_3 = Column(Float)\n",
    "    bid_price_4 = Column(Float)\n",
    "    bid_price_5 = Column(Float)\n",
    "\n",
    "    ask_price_1 = Column(Float)\n",
    "    ask_price_2 = Column(Float)\n",
    "    ask_price_3 = Column(Float)\n",
    "    ask_price_4 = Column(Float)\n",
    "    ask_price_5 = Column(Float)\n",
    "\n",
    "    bid_volume_1 = Column(Float)\n",
    "    bid_volume_2 = Column(Float)\n",
    "    bid_volume_3 = Column(Float)\n",
    "    bid_volume_4 = Column(Float)\n",
    "    bid_volume_5 = Column(Float)\n",
    "\n",
    "    ask_volume_1 = Column(Float)\n",
    "    ask_volume_2 = Column(Float)\n",
    "    ask_volume_3 = Column(Float)\n",
    "    ask_volume_4 = Column(Float)\n",
    "    ask_volume_5 = Column(Float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "parliamentary-bouquet",
   "metadata": {},
   "outputs": [],
   "source": [
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "mental-processor",
   "metadata": {},
   "outputs": [],
   "source": [
    "DbTickData.__table__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dying-kazakhstan",
   "metadata": {},
   "outputs": [],
   "source": [
    "Database = sessionmaker(bind=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "traditional-geography",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = Database()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "balanced-liberia",
   "metadata": {},
   "outputs": [],
   "source": [
    "DbTickData.__table__.drop(engine)\n",
    "DbBarData.__table__.drop(engine)\n",
    "DbBarOverview.__table__.drop(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "confused-adrian",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "a=datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "secret-scroll",
   "metadata": {},
   "outputs": [],
   "source": [
    "one = DbBarData(symbol='IF888', exchange ='CFFEX', datetime =a, interval = '1h', volume = 0, open_interest =0, open_price = 0, high_price = 10, low_price =0.5, close_price = 0.5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "characteristic-regular",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.merge(one)\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "constant-capital",
   "metadata": {},
   "outputs": [],
   "source": [
    "DbBarData.__table__.drop(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "national-pastor",
   "metadata": {},
   "outputs": [],
   "source": [
    "for instance in db.query(DbTickData).order_by(DbTickData.datetime):\n",
    "    print(instance.datetime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "closed-siemens",
   "metadata": {},
   "outputs": [],
   "source": [
    "count = db.query(DbTickData).filter(\n",
    "            DbTickData.exchange == 'BINANCE'\n",
    "        ).delete()\n",
    "\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "supported-milan",
   "metadata": {},
   "outputs": [],
   "source": [
    "count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "favorite-cardiff",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
